package com.iflytek.application.common.springjdbc;


import com.iflytek.application.common.springdata.jdbc.utils.SuperBeanUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import javax.persistence.Table;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/***
 * Created by guanxf on 2015/12/16.
 */
public class JdbcTemplateOperation {
	public JdbcTemplate jdbcTemplateOperation;
	public JdbcTemplateOperation(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplateOperation=jdbcTemplate;
	}
	/***
	 * 查询所有的数据
	 * @param c
	 * @return
	 */
	public Pagination queryAll(Class<?> c,String sql)  {
		Pagination pagination = new Pagination();
		try {
			List<Map<String, Object>> list = jdbcTemplateOperation.queryForList(sql);
			List<Object> returnList=new ArrayList<Object>();
			for(int i=0;i<list.size();i++)
			{
				Map<String, Object> map = list.get(i);
				Object object= SuperBeanUtils.convertForObject(c, map);
				returnList.add(object);
			}
			pagination.setContent(returnList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pagination;
	}


	/**
	 * query for list
	 * @param jql
	 * @param c
	 * @return
	 */
	protected List<?> findSqlForList(String jql,  Class<?> c) {
		try {
			String fieldsStr = "";
			Field[] fields = c.getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				{
					if (i != fields.length - 1)
						fieldsStr += fields[i].getName() + ",";
					else
						fieldsStr += fields[i].getName();

				}
			}
			String sql = "select " + fieldsStr + " " + jql;
			System.out.println(sql);
			List<Map<String, Object>> list = jdbcTemplateOperation.queryForList(sql);
			List<Object> returnList = new ArrayList<Object>();
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map = list.get(i);
				Object object=SuperBeanUtils.convertForObject(c, map);
				returnList.add(object);
			}
			return returnList;
		} catch (Exception e) {
             e.printStackTrace();
		}
		return new ArrayList<Object>();
	}

	/***
	 * Save One
	 * @param tablename
	 * @param fields
	 * @param values
     * @return
     */
	public int saveObj(String tablename,String fields[],final String values[] ) 
	{
		String fstr="";
		String v="";
		for (int i = 0; i < fields.length&&!fields[i].equals("id"); i++) {
		{
			if (i != fields.length - 1)
				{fstr += fields[i]+ ",";
			    v+= "?,";}
			else
			{	fstr += fields[i];
			v+= "?";
			}

		}
 
	 }		
	 final String sql = "insert into "+tablename+"("+fstr+")"
				+ " values("+v+")";

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplateOperation.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

				for (int i = 0; i < values.length; i++) {
					ps.setString(i + 1, values[i]);
				}
				return ps;
			}

		}, keyHolder);
		// 获得 插入 id
		return keyHolder.getKey().intValue();
	}
 
	public int saveObj(String tablename,Object object) throws Exception
	{
		Class<?>  c=object.getClass();
		Field[] fields = c.getDeclaredFields();
		//String fieldsStr="(";
		//String valuesStr="(";
		String fieldAy[]=new String[fields.length-1];
		final String valuesAy[]=new String[fields.length-1]; 
		
		int j=0;
		for(int i=0;i<fields.length;i++)
		{
			String fname=fields[i].getName();
			if(!"id".equals(fname))
			{
			PropertyDescriptor pd = new PropertyDescriptor(fname,c);
			Method getMethod = pd.getReadMethod();//获得get方法
			Object o = getMethod.invoke(object);//执行get方法返回一个Object
			fieldAy[j]=fname;
			if(o!=null)
			{
				valuesAy[j]=o.toString();
			}else{
				valuesAy[j]="";
			}
			j++;
			
//			if(i==fields.length-1)
//			{
//				fieldAy[i]=fname;
//				valuesAy[i]=o.toString();
//				//fieldsStr+=fname+")";
//				//valuesStr+=o.toString()+")";
//			}else
//			{
//				//fieldsStr+=fname+",";
//				//valuesStr+=o.toString()+",";
//				valuesAy[i]=o.toString();
//			}
			}
			


		}
		PropertyDescriptor pd = new PropertyDescriptor("id",c);
		Method getMethod = pd.getReadMethod();//获得get方法
		Object o = getMethod.invoke(object);//执行get方法返回一个Object
		int id=Integer.parseInt(o.toString());
		if(id>0)
		{
			String dv="";
			for(int i=0;i<fieldAy.length;i++)
			{
				if(i!=fieldAy.length-1)
				{
					dv+=fieldAy[i]+"='"+valuesAy[i]+"',";
				}
				else
				{
					dv+=fieldAy[i]+"='"+valuesAy[i]+"'";
				}
			}
			String updateSql="update "+tablename+" set  "+dv+" where id="+id;
			System.out.println(updateSql);
			jdbcTemplateOperation.execute(updateSql);
			return id;
		}else
		{
		String fstr="";
		String v="";
		for (int i = 0; i < fieldAy.length; i++) 
		{
			{
				if (i != fieldAy.length - 1)
				{
					fstr += fieldAy[i]+ ",";
				    v+= "?,";
				}
				else
				{	
					fstr += fieldAy[i];
				    v+= "?";
				}
			}
		 }		
		 final String sql = "insert into "+tablename+"("+fstr+")"
					+ " values("+v+")";
            System.out.println(sql);
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplateOperation.update(new PreparedStatementCreator() {

				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

					for (int i = 0; i < valuesAy.length; i++) {
						ps.setString(i + 1, valuesAy[i]);
					}
					return ps;
				}

			}, keyHolder);
			// 获得 插入 id
			return keyHolder.getKey().intValue();
		}
	}
 
	public int saveObj(String tablename,String keyid,Object object) throws Exception
	{
		Class<?>  c=object.getClass();
		Field[] fields = c.getDeclaredFields();
		//String fieldsStr="(";
		//String valuesStr="(";
		String fieldAy[]=new String[fields.length-1];
		final String valuesAy[]=new String[fields.length-1]; 
		
		int j=0;
		for(int i=0;i<fields.length;i++)
		{
			String fname=fields[i].getName();
			if(!keyid.equals(fname))
			{
			PropertyDescriptor pd = new PropertyDescriptor(fname,c);
			Method getMethod = pd.getReadMethod();//获得get方法
			Object o = getMethod.invoke(object);//执行get方法返回一个Object
			fieldAy[j]=fname;
			if(o!=null)
			{
				valuesAy[j]=o.toString();
			}else{
				valuesAy[j]="";
			}
			j++;
			

			}
			


		}
		PropertyDescriptor pd = new PropertyDescriptor(keyid,c);
		Method getMethod = pd.getReadMethod();//获得get方法
		Object o = getMethod.invoke(object);//执行get方法返回一个Object
		int id=Integer.parseInt(o.toString());
		if(id>0)
		{
			String dv="";
			for(int i=0;i<fieldAy.length;i++)
			{
				if(i!=fieldAy.length-1){
					dv+=fieldAy[i]+"='"+valuesAy[i]+"',";
				}
				else
					{
					dv+=fieldAy[i]+"='"+valuesAy[i]+"'";
					}
			}
			String updateSql="update "+tablename+" set  "+dv+" where "+keyid+"="+id;
			System.out.println(updateSql);
			jdbcTemplateOperation.execute(updateSql);
			return id;
		}else
		{
		String fstr="";
		String v="";
		for (int i = 0; i < fieldAy.length; i++) {
			{
				if (i != fieldAy.length - 1)
					{fstr += fieldAy[i]+ ",";
				    v+= "?,";}
				else
				{	fstr += fieldAy[i];
				v+= "?";
				}

			}
	 
		 }		
		 final String sql = "insert into "+tablename+"("+fstr+")"
					+ " values("+v+")";
            System.out.println(sql);
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplateOperation.update(new PreparedStatementCreator() {

				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

					for (int i = 0; i < valuesAy.length; i++) {
						ps.setString(i + 1, valuesAy[i]);
					}
					return ps;
				}

			}, keyHolder);
			// 获得 插入 id
			return keyHolder.getKey().intValue();
		}
	}
	// 批量插入,  
    public List<?> saveOrUpdateAll(String tablename,final String keyid,Object object,final List<?> list) throws Exception { 
    	final Class<?>  c=object.getClass();
		final Field[] fields = c.getDeclaredFields();
		//String fieldsStr="(";
		//String valuesStr="(";
		final String fieldAy[]=new String[fields.length-1];
		//final String valuesAy[]=new String[fields.length-1]; 
		
		int j=0;
		for(int i=0;i<fields.length;i++)
		{
			String fname=fields[i].getName();
			if(!keyid.equals(fname))
			{
			//PropertyDescriptor pd = new PropertyDescriptor(fname,c);
			//Method getMethod = pd.getReadMethod();//获得get方法
			//Object o = getMethod.invoke(object);//执行get方法返回一个Object
			fieldAy[j]=fname;
			 
			j++;
			

			}
			


		}
		//PropertyDescriptor pd = new PropertyDescriptor(keyid,c);
		//Method getMethod = pd.getReadMethod();//获得get方法
		//Object o = getMethod.invoke(object);//执行get方法返回一个Object
		//int id=Integer.parseInt(o.toString());
		 
		String fstr="";
		String v="";
		for (int i = 0; i < fieldAy.length; i++) {
			{
				if (i != fieldAy.length - 1)
					{fstr += fieldAy[i]+ ",";
				    v+= "?,";}
				else
				{	fstr += fieldAy[i];
				v+= "?";
				}

			}
	 
		 }		
		 final String sql = "insert into "+tablename+"("+fstr+")"
					+ " values("+v+")";
		 
    	jdbcTemplateOperation.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int in)
					throws SQLException {
				String valuesAy[] = new String[fields.length - 1];
				int j = 0;
				for (int i = 0; i < fields.length; i++) {
					String fname = fields[i].getName();
					if (!keyid.equals(fname)) {
						PropertyDescriptor pd = null;
						try {
							pd = new PropertyDescriptor(fname, c);
						} catch (IntrospectionException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						Method getMethod = pd.getReadMethod();//获得get方法
						Object o = null;
						try {
							o = getMethod.invoke(list.get(in));
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						//执行get方法返回一个Object
						fieldAy[j] = fname;
						if (o != null) {
							valuesAy[j] = o.toString();
						} else {
							valuesAy[j] = "";
						}
						j++;
					}
				}
				for (int k = 0; k < valuesAy.length; k++) {
					ps.setString(k + 1, valuesAy[k]);
				}
			}

			@Override
			public int getBatchSize() {
				return list.size();
			}
		});
                //哪个方法可以获得list中每个test对象的id?我不想重新进行查询  
         return list;  
		
    }  

	
	public Pagination getPaginationList(Class<?> c,String tablename, String where,String  order,  int pCurrentPage,  int pPageSize)  {
		 Pagination pagination = new Pagination();
				try {
					String  countSql="select count(*) from "+tablename+" "+(where.length()>0?"where":"")+" "+where;
					String orderSql=order.length()>0?" order by "+order:"";
					int firstRow = (pCurrentPage - 1) * pPageSize ;		
					String executeSql ="select * from "+tablename+" "+(where.length()>0?"where":"")+" "+where +orderSql+" limit " + firstRow + " , " + pPageSize;					
					int mRowCount = jdbcTemplateOperation.queryForObject(countSql, Integer.class);
					if(mRowCount>0){
						List<Map<String, Object>> list = jdbcTemplateOperation.queryForList(executeSql);
						List<Object> returnList=new ArrayList<Object>();
						for(int i=0;i<list.size();i++)
						{
							Map<String, Object> map = list.get(i);
							Object object=SuperBeanUtils.convertForObject(c, map);
							returnList.add(object);
						}
						pagination.setContent(returnList);
					}
					pagination.setSize(pPageSize);
					pagination.setNumber(pCurrentPage);
					pagination.setTotalElements(mRowCount);
				} catch (Exception e) {
					e.printStackTrace();
				} 			
		return pagination;
	}




	public Pagination getPageLibListNOT_index(Class<?> c,String tablename,String where,String order,int pageNum,int pageSize) {
		Pagination pagination=getPaginationListNOT_index(c,tablename,where,order ,pageNum,pageSize);
		return pagination;
	}	
	public Pagination getPaginationListNOT_index(Class<?> c,String tablename, String where,String  order,  int pCurrentPage,  int pPageSize)  {
		
		 Pagination pagination = new Pagination();
				try {
					String  countSql="select count(*) from "+tablename+" "+(where.length()>0?"where":"")+" "+where;
					String orderSql=order.length()>0?" order by "+order:"";
					int firstRow = (pCurrentPage - 1) * pPageSize ;		
					String executeSql ="select * from "+tablename+" "+(where.length()>0?"where":"")+" "+where +orderSql+" limit " + firstRow + " , " + pPageSize;					
					System.out.println("query:>"+executeSql);
					int mRowCount = jdbcTemplateOperation.queryForObject(countSql, Integer.class);
					List<Map<String, Object>> list = jdbcTemplateOperation.queryForList(executeSql);
				    List<Object> returnList=new ArrayList<Object>();
				        for(int i=0;i<list.size();i++)
				        {
							Object obj = c.newInstance();
							Field[] fields = c.getDeclaredFields();
							Map<String, Object> map = list.get(i);
							Object object=SuperBeanUtils.convertForObject(c, map);
							returnList.add(object);
				        }				
					pagination.setContent(returnList);
					pagination.setSize(pPageSize);
					pagination.setNumber(pCurrentPage);
					pagination.setTotalElements(mRowCount);
				} catch (Exception e) {
					e.printStackTrace();
				} 			
		return pagination;
	}	
 
 


 
	public Object getObject(Class<?> c,String table,int id)
	{
		Object obj=null;
		try {
		if(id==0) return obj;
		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table+" where id="+id);
		Map<String, Object> map = list.get(0);
			Object object=SuperBeanUtils.convertForObject(c, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	public Object getNoteObject(Class<?> c,String table,int parentid)
	{
		Object obj=null;
		try {
		if(parentid==0)
			return obj;
		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table+" where pareid="+parentid);
		Map<String, Object> map = list.get(0);
			obj=SuperBeanUtils.convertForObject(c, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	public Object getObject(Class<?> c,String table,String where)
	{
		Object obj=null;
		try {
			
		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table+" where "+where);
		if(list.size()>0)
		{
		Map<String, Object> map = list.get(0);
			obj=SuperBeanUtils.convertForObject(c, map);
		}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return obj;
	}
	
	public Object getObject(Class<?> c,String table)
	{
		Object obj=null;
		try {
		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table);
		if(list.size()>0)
		{
		Map<String, Object> map = list.get(0);
			obj=SuperBeanUtils.convertForObject(c, map);
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	public Object getObject(Class<?> c,String table,String where[],Object whereVal[]) throws InvocationTargetException, NoSuchMethodException, InstantiationException, ParseException, IllegalAccessException {
		Object obj=null;
		try {
		String wv="";
		for(int i=0;i<where.length;i++)
		{
			if(i==0)
			{
				wv+=where[i]+"='"+whereVal[i]+"'";
			}else
			{
				wv+=" and "+where[i]+"='"+whereVal[i]+"'";
			}
		}

		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table+" where "+wv);
		if(list.size()>0)
		{
		Map<String, Object> map = list.get(0);
			obj=SuperBeanUtils.convertForObject(c, map);
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}	
	public Object getObject(Class<?> c,String table,String where,Object whereVal)
	{
		Object obj=null;
		try {
	 
		List<Map<String, Object>> list = jdbcTemplateOperation.queryForList("select * from "+table+" where "+where+"="+"'"+whereVal+"'");
		if(list.size()>0) {
			Map<String, Object> map = list.get(0);
			obj=SuperBeanUtils.convertForObject(c, map);
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}		
	private String htmlspecialchars(String str) {
		str = str.replaceAll("&", "&amp;");
		str = str.replaceAll("<", "&lt;");
		str = str.replaceAll(">", "&gt;");
		str = str.replaceAll("\"", "&quot;");
		return str;
	}
	
	//多表查询
	public Pagination getPageLibListMUL(Class<?> c,String select,String tablename,String where,String order,int pageNum,int pageSize) {
		Pagination pagination=getPaginationListMUL(c,select,tablename,where,order ,pageNum,pageSize);
		return pagination;
	}	
	
	public Pagination getPaginationListMUL(Class<?> c,String select,String tablename, String where,String  order,  int pCurrentPage,  int pPageSize)  {
			
		 Pagination pagination = new Pagination();
				try {
					String  countSql="select count(*) from "+tablename+" "+(where.length()>0?"where":"")+" "+where;
					String orderSql=order.length()>0?" order by "+order:"";
					int firstRow = (pCurrentPage - 1) * pPageSize ;		
					String executeSql ="select "+select+" from "+tablename+" "+(where.length()>0?"where":"")+" "+where +orderSql+" limit " + firstRow + " , " + pPageSize;					
					System.out.println("query:>"+executeSql);
					int mRowCount = jdbcTemplateOperation.queryForObject(countSql, Integer.class);
					List<Map<String, Object>> list = jdbcTemplateOperation.queryForList(executeSql);
				    List<Object> returnList=new ArrayList<Object>();
				        for(int i=0;i<list.size();i++)
				        {
							Map<String, Object> map = list.get(i);
							Object	obj=SuperBeanUtils.convertForObject(c, map);
							returnList.add(obj);
				        }				
					pagination.setContent(returnList);
					pagination.setSize(pPageSize);
					pagination.setNumber(pCurrentPage);
					pagination.setTotalElements(mRowCount);
				} catch (Exception e) {
					e.printStackTrace();
				} 			
		return pagination;
	}	
}
